#include "sqlmgr.h"
#include <QCoreApplication>
#include <QSqlQuery>
#include <QDebug>
#include <QSqlError>
#include <QSqlRecord>
#include <QDateTime>
SqlMgr* SqlMgr::instance =nullptr;
SqlMgr::SqlMgr() {}

void SqlMgr::init()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(QCoreApplication::applicationDirPath()+"/db/book.db");
    m_db.open();
    // qDebug()<<QSqlDatabase::drivers();
}



bool SqlMgr::login(QString strUser, QString strPassWord,int &userId)
{
    QSqlQuery query(m_db);
    QString strSql = QString("select * from user where username='%1' and password='%2' ")
                         .arg(strUser)
                         .arg(strPassWord);
    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }else{
        success = query.next();
        if(success){
            userId = query.value(0).toInt();
        }
    }
    return success;
}

QVector<QStringList> SqlMgr::getUsers(QString strCondition)
{
    //username like '%i%' or nickname like '%小%'
    QSqlQuery query(m_db);
    QString strSql = QString("select * from user %1").arg(strCondition);
    QVector<QStringList> vec;
    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }else{
        int iCols = query.record().count();
        QStringList l;
        while(query.next()){
            l.clear();
            for(int i = 0;i<iCols;i++){
                l<<query.value(i).toString();
            }
            vec.push_back(l);
        }
    }
    return vec;
}

void SqlMgr::addUser(QVector<QStringList> v)
{
    m_db.transaction();
    QSqlQuery query(m_db);
    for(QStringList it:v){
        QString strSql = QString("insert into user values (null,'%1','%2','%3','%4','%5','%6');")
                             .arg(it[0])
                             .arg(it[1])
                             .arg(it[2])
                             .arg(it[3])
                             .arg(it[4])
                             .arg(it[5]);
        bool success = query.exec(strSql);
        if(!success)
        {
            qDebug()<<query.lastError().text();
            qDebug()<<strSql;
        }
    }
    m_db.commit();

}

void SqlMgr::delUser(QString strId)
{
    QSqlQuery query(m_db);
    QString delete_sql = "delete from user where userid = ?";
    query.prepare(delete_sql);
    query.addBindValue(strId);
    if(!query.exec())
    {
        qDebug()<<query.lastError().text();
    }
}

QVector<QStringList> SqlMgr::getBooks(QString strCondition)
{
    QSqlQuery query(m_db);
    QString strSql = QString("select * from book %1").arg(strCondition);
    QVector<QStringList> vec;
    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }else{
        int iCols = query.record().count();
        QStringList l;
        while(query.next()){
            l.clear();
            for(int i = 0;i<iCols;i++){
                l<<query.value(i).toString();
            }
            vec.push_back(l);
        }
    }
    return vec;
}

void SqlMgr::addBook(QVector<QStringList> vec)
{
    if(vec.size()==0){
        return;
    }
    auto ldata = vec[0];
    QSqlQuery query(m_db);
    QString strSql = QString("insert into book "
                             "values(null,'%1','%2','%3','%4','%5',%6,'')")
                         .arg(ldata[1])
                         .arg(ldata[2])
                         .arg(ldata[3])
                         .arg(ldata[4])
                         .arg(ldata[5])
                         .arg(ldata[6]);

    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }
}

void SqlMgr::updateBooks(QStringList ldata)
{
    QSqlQuery query(m_db);
    QString strSql = QString("update book set "
                             "name='%1',press='%2',type1='%3',type2='%4',type3='%5',count='%6'"
                             "where bookid='%7'")
                         .arg(ldata[1])
                         .arg(ldata[2])
                         .arg(ldata[3])
                         .arg(ldata[4])
                         .arg(ldata[5])
                         .arg(ldata[6])
                         .arg(ldata[0]);
    qDebug()<<strSql;
    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }
    // return success;
}

QString SqlMgr::delBook(QString strId)
{
    QString strRet;
    QSqlQuery query(m_db);
    QString delete_sql = "delete from book where bookid = ?";
    query.prepare(delete_sql);
    query.addBindValue(strId);
    if(!query.exec())
    {
        qDebug()<<query.lastError().text();
        strRet = "删除失败,请检查";
    }
    return strRet;
}

QString SqlMgr::backBook(QString strUserId, QString strBookId)
{
    QSqlQuery query(m_db);
    QString sql = QString("update book set count=count+1 where bookid = %1 ;")
                      .arg(strBookId);

    if(!query.exec(sql))
    {
        qDebug()<<query.lastError().text();
    }
    sql = QString("delete from record where bookid = %1 and userid = %2")
                      .arg(strBookId)
                      .arg(strUserId);
    qDebug()<<sql;
    if(!query.exec(sql))
    {
        qDebug()<<query.lastError().text();
    }
    return QString("");
}

QString SqlMgr::borrowBook(QString strUserId ,QString strBookId)
{
    //实现图书借阅
    QSqlQuery query(m_db);
    QString sql = QString("update book set count=count-1 where bookid = %1 ;")
                      .arg(strBookId);
    if(!query.exec(sql))
    {
        qDebug()<<query.lastError().text();
    }
    sql = QString("insert into record VALUES(null,%1,%2,%3,%4)")
                      .arg(strBookId)
                      .arg(strUserId)
                      .arg(QDateTime::currentSecsSinceEpoch())
                      .arg(QDateTime::currentSecsSinceEpoch()+3600*24*10);
    // qDebug()<<sql;
    if(!query.exec(sql))
    {
        qDebug()<<query.lastError().text();
    }
    return QString("");
}

QVector<QStringList> SqlMgr::getRecord(QString strCondition)
{
    QSqlQuery query(m_db);
    QString strSql = QString("select * from record join user using(userid) join book using(bookid) %1").arg(strCondition);
    qDebug()<<strSql;
    QVector<QStringList> vec;
    bool success = query.exec(strSql);
    if(!success){
        qDebug()<<query.lastError().text();
    }else{
        int iCols = query.record().count();
        QStringList l;
        while(query.next()){
            l.clear();
            for(int i = 0;i<iCols;i++){
                l<<query.value(i).toString();
            }
            vec.push_back(l);
        }
    }
    return vec;
}

void SqlMgr::clearRecord()
{
    QSqlQuery query(m_db);
    QString sql = "delete from record;delete from sqlite_sequence where name = 'book';";
    if(!query.exec(sql))
    {
        qDebug()<<query.lastError().text();
    }
}

